package com;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库操作类
 */
public class DbUtil {
    public static final String URL = "jdbc:mysql://localhost:3306/md_wb?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";
    public static final String USER = "root";
    public static final String PASSWORD = "root";

    public static void main(String[] args) throws Exception {
        //1.加载驱动程序
//        Class.forName("com.mysql.cj.jdbc.Driver");
        //2. 获得数据库连接
//        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        //3.操作数据库，实现增删改查
//        String sql = "insert into blog (content,publishtime,userid) values(?,sysdate(),?)";
//
//        for (int i = 0; i < 100; i++) {
//            PreparedStatement  ptmt = conn.prepareStatement(sql); //预编译SQL，
//            ptmt.setString(1,"微博内容"+i);
//            ptmt.setString(2,(i+1)+"");
//            ptmt.execute();
//            System.out.println("第"+i+"条记录添加完毕");
//        }
//        addBlog("xxx");
        boolean isS = false;
Users user = new Users();
        user.setUsername("李李");
        user.setPassword("123");
        user.setSex(0);
        int isSupper = isS == true ? 1 : 0;
        user.setSupper(isSupper);
        user.setPicpath("img/java.png");
        addUser(user);
    }
    /**
     * 获得数据连接对象
     * @return
     * @throws Exception
     */
    public static Connection  getConn() throws Exception {
        //1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获得数据库连接
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        return conn;
    }

    /**
     *添加用户
     */
    public static void addUser(Users users){
        try {
            Connection conn = DbUtil.getConn();
            String sql = "insert into users (username,password,sex,supper,picpath) values(?,?,?,?,?)";
            PreparedStatement  ptmt = conn.prepareStatement(sql); //预编译SQL，
            ptmt.setString(1,users.getUsername());
            ptmt.setString(2,users.getPassword());
            ptmt.setInt(3,users.getSex());
            ptmt.setInt(4,users.getSupper());
            ptmt.setString(5,users.getPicpath());
            boolean execute = ptmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 添加记录
     * @param content
     */
    public void addBlog(String content){
        try {
            Connection conn = DbUtil.getConn();
            String sql = "insert into blog (content,publishtime,userid) values(?,sysdate(),?)";
            PreparedStatement  ptmt = conn.prepareStatement(sql); //预编译SQL，
            ptmt.setString(1,content);
            ptmt.setString(2,1+"");//
            boolean execute = ptmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 获得博客总记录数
     * @return
     * @throws Exception
     */
    public Integer getBlogCount() throws Exception {
        Connection conn = DbUtil.getConn();
        //预编译
        String sql = "SELECT count(*) count FROM blog ";
        PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，
        ResultSet rs = ptmt.executeQuery();
        int count = 0;
        while(rs.next()){
            count = rs.getInt("count");
        }
        return count;
    }
    /**
     * 某页的记录集合
     * @return
     * @throws Exception
     */
    public List<Blog> getBlogList(Page page) throws Exception {
        List<Blog> blogList = new ArrayList<Blog>();
        //2. 获得数据库连接
        Connection conn = DbUtil.getConn();
        //预编译
        String sql = "SELECT * FROM blog limit ?,?";
        PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，
        ptmt.setInt(1,page.getStart());
        ptmt.setInt(2,page.getPageSize());
        ResultSet rs = ptmt.executeQuery();
        Blog blogTemp=null;
        while(rs.next()){
            blogTemp = new Blog();
            blogTemp.setId(rs.getLong("id"));
            blogTemp.setContent(rs.getString("content"));
            blogTemp.setPublishtime(rs.getDate("publishtime"));
            blogTemp.setUserid(rs.getLong("userid"));
            blogList.add(blogTemp);
        }
        return blogList;
    }
    /**
     * 获得用户对象
     * @param
     * @return
     * @throws Exception
     */
    public Users getUser(String username,String password) throws Exception {

        Connection conn = DbUtil.getConn();
        //预编译
        String sql = "SELECT * FROM users where username=? and password = ?";
        PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，
        ptmt.setString(1,username);
        ptmt.setString(2,password);
        ResultSet rs = ptmt.executeQuery();
        Users usersTemp = null;//null;
        while(rs.next()){
            usersTemp = new Users();
            System.out.println(rs.getString("username")+" 年龄："+rs.getInt("age"));
            usersTemp.setUsername(rs.getString("username"));
            usersTemp.setPassword(rs.getString("password"));
            usersTemp.setAge(rs.getInt("age"));
//            .....
        }
        return usersTemp;
    }


}
